﻿using System;
using System.Data;
using System.Data.Common;
using System.Data.SqlServerCe;

using System.IO;

namespace NoteBook
{
	/// <summary>

	/// </summary>
	class MyDB
	{
        // Описание SQL языка и команд для SQLCE
        // http://msdn.microsoft.com/en-us/library/ms173372(v=SQL.100).aspx
		private string DBPath;
        public DataSet DS = new DataSet();

        public DataTable TableNotes = new DataTable();
        public SqlCeDataAdapter DANotes = new SqlCeDataAdapter();
        private SqlCeCommandBuilder CBNotes;

        private SqlCeConnection DBCon;

        public MyDB(string Path)
        {
            DBPath = Path;
            DBCon = new SqlCeConnection("Data Source = " + DBPath);
            TableNotes.TableName = "Notes";
            DS.Tables.Add(TableNotes);
            if (!File.Exists(DBPath)) CreateDB();
        }
        public void FillTables()
        {            
            DANotes.SelectCommand = new SqlCeCommand("select * from Notes", DBCon);
            CBNotes = new SqlCeCommandBuilder(DANotes);
            DANotes.Fill(TableNotes);
            DS.Tables["Notes"].Columns["OrderNum"].AutoIncrement = true;
            DS.Tables["Notes"].DefaultView.Sort = "OrderNum";
        }
        public void UpdateTables()
        {            
            DANotes.Update(TableNotes);
        }
		public void CreateDB()
		{
            SqlCeEngine Eng = new SqlCeEngine("Data Source = " + DBPath);
            Eng.CreateDatabase();
            Eng.Dispose();

            DBCon.Open();
            using (SqlCeCommand com = new SqlCeCommand())
			{
                com.Connection = DBCon;
				com.CommandText = @"CREATE TABLE Notes (
					ID integer PRIMARY KEY IDENTITY,
                    IDCat integer NOT NULL DEFAULT 0,
					OrderNum integer NOT NULL DEFAULT 0,
					Name nvarchar(250) NOT NULL,
					Text ntext);";
				com.CommandType = CommandType.Text;
				com.ExecuteNonQuery();
			}
            DBCon.Close();
        }
        public void OptimazeDB()
        {
            SqlCeEngine Eng = new SqlCeEngine("Data Source = " + DBPath);
            Eng.Shrink();
            Eng.Dispose();
        }
	}
}